import datetime
from sqlalchemy import Table, Column, Integer, String, ForeignKey, Date, Text, DateTime, Boolean
from sqlalchemy.orm import relationship
from flask_appbuilder import Model


class Tag(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True, nullable=False)

    def __repr__(self):
        return self.name

class AppMenu(Model):
     id = Column(Integer, primary_key=True)
     text = Column(String(50), unique=True, nullable=False)
     icon = Column(String(50))
     parent_id = Column(Integer, ForeignKey('app_menu.id'), nullable=False)
     parent = relationship("AppMenu")

     def __repr__(self):
         return self.text


class TagAppMenuRelationship(Model):
     id = Column(Integer, primary_key=True)
     tag_id = Column(Integer, ForeignKey('tag.id'), nullable=False)
     tag = relationship("Tag")
     menu_id = Column(Integer, ForeignKey('app_menu.id'), nullable=False)
     menu = relationship("AppMenu")

     def __repr__(self):
         return self.id


class Post(Model):
    id = Column(Integer, primary_key=True)
    title = Column(String(150), nullable=False)
    summary = Column(String(150), nullable=False)
    content = Column(Text, nullable=False)
    created = Column(DateTime, nullable=False)
    modified = Column(DateTime, nullable=False)
    author_id = Column(Integer, ForeignKey('ab_user.id'), nullable=False)
    author = relationship("User")
    published = Column(Boolean, nullable=False)

    def __repr__(self):
        return self.title


class TagPostRelationship(Model):
     id = Column(Integer, primary_key=True)
     tag_id = Column(Integer, ForeignKey('tag.id'), nullable=False)
     tag = relationship("Tag")
     post_id = Column(Integer, ForeignKey('post.id'), nullable=False)
     post = relationship("Post")

     def __repr__(self):
         return self.id


class Department(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True, nullable=False)

    def __repr__(self):
        return self.name


class Function(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True, nullable=False)

    def __repr__(self):
        return self.name


class Benefit(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True, nullable=False)

    def __repr__(self):
        return self.name

assoc_benefits_employee = Table('benefits_employee', Model.metadata,
                                  Column('id', Integer, primary_key=True),
                                  Column('benefit_id', Integer, ForeignKey('benefit.id')),
                                  Column('employee_id', Integer, ForeignKey('employee.id'))
)


def today():
    return datetime.datetime.today().strftime('%Y-%m-%d')


class EmployeeHistory(Model):
    id = Column(Integer, primary_key=True)
    department_id = Column(Integer, ForeignKey('department.id'), nullable=False)
    department = relationship("Department")
    employee_id = Column(Integer, ForeignKey('employee.id'), nullable=False)
    employee = relationship("Employee")
    begin_date = Column(Date, default=today)
    end_date = Column(Date)


class Employee(Model):
    id = Column(Integer, primary_key=True)
    full_name = Column(String(150), nullable=False)
    address = Column(Text(250), nullable=False)
    fiscal_number = Column(Integer, nullable=False)
    employee_number = Column(Integer, nullable=False)
    department_id = Column(Integer, ForeignKey('department.id'), nullable=False)
    department = relationship("Department")
    function_id = Column(Integer, ForeignKey('function.id'), nullable=False)
    function = relationship("Function")
    benefits = relationship('Benefit', secondary=assoc_benefits_employee, backref='employee')

    begin_date = Column(Date, default=datetime.date.today(), nullable=True)
    end_date = Column(Date, default=datetime.date.today(), nullable=True)

    def __repr__(self):
        return self.full_name


class SerialPortCategory(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True, nullable=False)

    def __repr__(self):
        return self.name


class SerialPort(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(150), nullable=False)
    port = Column(String(250), nullable=False)
    category_id = Column(Integer, ForeignKey('serial_port_category.id'), nullable=False)
    category = relationship("SerialPortCategory")

    readme = Column(Text(250), nullable=False)

    def __repr__(self):
        return self.name


class Tray(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True, nullable=False)
    action = Column(String(50), unique=True, nullable=False)

    def __repr__(self):
        return "%s do '%s'" % (self.name, self.action)


class AppSetting(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    # 机器臂
    robot_id = Column(Integer, ForeignKey('serial_port.id'), nullable=False)
    robot = relationship("SerialPort", foreign_keys=[robot_id])
    # 检测板
    board_id = Column(Integer, ForeignKey('serial_port.id'), nullable=False)
    board = relationship("SerialPort", foreign_keys=[board_id])
    # PAL元件
    cooler_id = Column(Integer, ForeignKey('serial_port.id'), nullable=False)
    cooler = relationship("SerialPort", foreign_keys=[cooler_id])
    readme = Column(Text(256))


class Profile(Model):
    u'''配置文件'''
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    readme = Column(Text(256))
    default_tray_id = Column(Integer, ForeignKey('tray.id'), nullable=False)
    default_tray = relationship("Tray")


    def __repr__(self):
        return self.name


class CheckUnit(Model):
    u'''检查单元，一个检查单元包括黑体和检测电路'''
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    # 黑体
    black_id = Column(Integer, ForeignKey('serial_port.id'), nullable=False)
    black = relationship("SerialPort", foreign_keys=[black_id])
    # profile
    profile_id = Column(Integer, ForeignKey('profile.id'), nullable=False)
    profile = relationship("Profile")
    temp = Column(Integer) #该黑体的温度
    readme = Column(Text(256))

    def __repr__(self):
        return self.name


class ResisterRange(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    min = Column(Integer)
    max = Column(Integer)
    readme = Column(Text(256))

    def __repr__(self):
        return self.name


class Selector(Model):
    u'''检测过滤条件，一个profile对应多个过滤条件。weight决定了过滤的优先级，weight越大，越优先过滤。'''
    id = Column(Integer, primary_key=True)
    name = Column(String(150), nullable=False)
    # profile
    profile_id = Column(Integer, ForeignKey('profile.id'), nullable=False)
    profile = relationship("Profile")
    # tray
    tray_id = Column(Integer, ForeignKey('tray.id'), nullable=False)
    tray = relationship("Tray")
    # resister a
    resister_a_id = Column(Integer, ForeignKey('resister_range.id'), nullable=False)
    resister_a = relationship("ResisterRange", foreign_keys=[resister_a_id])
    # resister b
    resister_b_id = Column(Integer, ForeignKey('resister_range.id'), nullable=False)
    resister_b = relationship("ResisterRange", foreign_keys=[resister_b_id])

    weight = Column(Integer)
    readme = Column(Text(256))

    def __repr__(self):
        return self.name


class VoltageRange(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    min = Column(Integer)
    max = Column(Integer)
    readme = Column(Text(256))

    def __repr__(self):
        return self.name


class Condition(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(150), nullable=False)
    # profile
    profile_id = Column(Integer, ForeignKey('profile.id'), nullable=False)
    profile = relationship("Profile")
    # check unit
    unit_id = Column(Integer, ForeignKey('check_unit.id'), nullable=False)
    unit = relationship("CheckUnit")
    # selector
    selector_id = Column(Integer, ForeignKey('selector.id'), nullable=False)
    selector = relationship("Selector")
    # a
    voltage_a_id = Column(Integer, ForeignKey('voltage_range.id'), nullable=False)
    voltage_a = relationship("VoltageRange", foreign_keys=[voltage_a_id])
    # b
    voltage_b_id = Column(Integer, ForeignKey('voltage_range.id'), nullable=False)
    voltage_b = relationship("VoltageRange", foreign_keys=[voltage_b_id])

    readme = Column(Text(256))


    def __repr__(self):
        return self.name


class Vendor(Model):
    id = Column(Integer, primary_key=True)
    name = Column(String(128))
    code = Column(String(128))
    address = Column(String(128))
    readme = Column(Text(256))

    def __repr__(self):
        return self.name


# 生产记录
class Batch(Model):
    u'''批次'''
    id = Column(Integer, primary_key=True)
    name = Column(String(128))
    # vendor
    vendor_id = Column(Integer, ForeignKey('vendor.id'), nullable=False)
    vendor = relationship("Vendor", foreign_keys=[vendor_id])
    # profile
    profile_id = Column(Integer, ForeignKey('profile.id'), nullable=False)
    profile = relationship("Profile", foreign_keys=[profile_id])

    created = Column(DateTime)
    finished = Column(DateTime)
    readme = Column(String(256))

    def __repr__(self):
        return self.name


class Qualified(Model):
    id = Column(Integer, primary_key=True)
    # vendor
    vendor_id = Column(Integer, ForeignKey('vendor.id'), nullable=False)
    vendor = relationship("Vendor", foreign_keys=[vendor_id])
    # profile
    profile_id = Column(Integer, ForeignKey('profile.id'), nullable=False)
    profile = relationship("Profile", foreign_keys=[profile_id])

    # selector_id = Column(Integer)
    resister_a = Column(Integer)
    resister_b = Column(Integer)


    def __repr__(self):
        return str(self.id)


class CheckResult(Model):
    id = Column(Integer, primary_key=True)
    # qualified
    qualified_id = Column(Integer, ForeignKey('qualified.id'), nullable=False)
    qualified = relationship("Qualified")
    # condition
    condition_id = Column(Integer, ForeignKey('condition.id'), nullable=False)
    condition = relationship("Condition")
    temp = Column(Integer)
    voltage_a = Column(Integer)
    voltage_b = Column(Integer)

    def __repr__(self):
        return self.voltage_a
